# -*- coding: utf-8 -*-
import os
import pyodbc
import sqlite3
import re
import sys
import steeltts
import pyaudio
import wave
from io import BytesIO

class DBOption(object):    
    def __init__(self):
        self.parameterDict = {
            'connDriverChecked' : False, 
            'dbType' : "SQLSERVER",  #根据类型值决定驱动及具体操作
            'dbPathAndName' : "setting.db", 
            'dbServer' : "数据库服务器地址,端口号", 
            'dataBase' : '数据库名', 
            'dbUserName' : "登录名",  # 用户名  
            'dbUserPwd' : "登录密码",  # 密码  
            'dbTableName' : "general_string_replace", 
            'sqlStatement' : None, #sql语句
            'timeStamp' : 0,  #时间戳
            'intervalCheck' : 30000,  #后台检查是否该断开数据库连接间隔时间
            'dbConnTimeOut' : 260000,  #超时则主动断开数据库连接
            'dbConnected' : False #是否已断开
        }
        #搜索最新版本odbc sql server驱动
        odbcDrivers = pyodbc.drivers()
        self.parameterDict['odbcDriver'] = None
        if len(odbcDrivers) == 1:
            self.parameterDict['odbcDriver'] = odbcDrivers[0]
        elif len(odbcDrivers) > 1:
            n2a = 0
            n2b = 0
            s2a = None
            s2b = None
            s2c = None
            for s3a in odbcDrivers:
                reSearch1 = re.search('(?:.* )?ODBC \D*(\d+.?\d*).* SQL SERVER(?: .*)?', s3a.upper())                
                if reSearch1 != None:
                    n3a = float(reSearch1.groups()[0])
                    if n3a > n2a:
                        n2a = n3a
                        s2a = s3a
                        continue
                reSearch2 = re.search('(?:.* )?SQL SERVER(?: | .* )NATIVAL CLIENT \D*(\d+.?\d*)\D*', s3a.upper())
                if reSearch2 != None:
                    n3b = float(reSearch2.groups()[0])
                    if n3b > n2b:
                        n2b = n3b
                        s2b = s3a
                        continue
                reSearch3 = re.search('(?:.* )?(SQL SERVER)(?: .*)?', s3a.upper())
                if reSearch3 != None:
                    s2c = s3a
            if s2a != None:
                self.parameterDict['odbcDriver'] = s2a
            elif s2b != None:
                self.parameterDict['odbcDriver'] = s2b
            elif s2c != None:
                self.parameterDict['odbcDriver'] = s2c
        
        self.conn = None
        self.cursor = None
        self.sqlResult = None

    #传递参数
    def set(self, paraName, paraValue):
        b1a = False
        for s2a in self.parameterDict.keys():
            if paraName.upper() == s2a.upper():
                b1a = True
                break
        if b1a == True:
            #如果参数数据类型不匹配则返回fale
            if self.parameterDict[paraName] != None and paraValue != None:
                if type(self.parameterDict[paraName]) != type(paraValue):
                    return(False )
            #如果不是不需改变数据库连接的参数则断开数据库连接
            b2a = False
            for s3a in ['sqlStatement','timeStamp']:
                if paraName.upper() == s3a.upper():
                    b2a = True
                    break
            if b2a == False:
                if self.parameterDict[paraName] != paraValue:
                    if self.parameterDict['dbConnected'] == True:
                        try:
                            self.conn.close()
                        except:
                            pass
                        self.parameterDict['dbConnected'] = False
            self.parameterDict[paraName] = paraValue
            return(True)
        return(False)
       
    def connDB(self):
        #如处于已连接状态则断开
        if self.parameterDict['dbConnected'] == True:
            try:
                self.conn.close()
            except:
                pass
        if self.parameterDict['dbType'].upper() == 'SQLSERVER':
            try:
                self.conn = pyodbc.connect(
                    'DRIVER=' + self.parameterDict['odbcDriver'] + ';'
                    'SERVER=' + self.parameterDict['dbServer'] + ';'
                    'DATABASE=' + self.parameterDict['dataBase'] + ';'
                    'UID=' + self.parameterDict['dbUserName'] + ';'
                    'PWD=' + self.parameterDict['dbUserPwd']
                    )
                self.cursor = self.conn.cursor()
            except:
                self.parameterDict['dbConnected'] = False
                return(False)
            else:
                self.parameterDict['dbConnected'] = True
                return(True)
        elif self.parameterDict['dbType'].upper() == 'SQLITE':
            try:
                self.conn = sqlite3.connect(self.parameterDict['dbPathAndName'])
                self.cursor = self.conn.cursor()
            except:
                self.parameterDict['dbConnected'] = False
                return(False)
            else:
                self.parameterDict['dbConnected'] = False
                return(True)
        else:
            return(False)
    
    def sqlExecute(self):
        #如果处于连接状态则直接发送sql语句，否则则先连接
        if self.parameterDict['dbConnected'] == True:
            executeResult = self.trySQLExecute(self.parameterDict['sqlStatement'])
        else:
            if self.connDB() == True:
                executeResult = self.trySQLExecute(self.parameterDict['sqlStatement'])
            else:
                self.sqlResult = None
                return(False)
        #如果execute返回'08S01'错误，则重试连接一次
        if executeResult == '08S01':
            if self.connDB() == True:
                executeResult = self.trySQLExecute(self.parameterDict['sqlStatement'])
            else:
                return(False)
        
        if executeResult == True:
            try:
                self.sqlResult = self.cursor.fetchall()
            except:
                self.sqlResult = None
            return(True)
        return(executeResult)
        
    def trySQLExecute(self, sqlStatement):
        try:            
            self.cursor.execute(sqlStatement)
            if sqlStatement.upper().find('SELECT ') == -1:
                self.conn.commit()
        except:
            self.sqlResult = None
            if '08S01' in str(sys.exc_info()[1]):
                return('08S01')
        return(True)

class TextToSpeech(object):
    def __init__(self):
        self.parameterDict = {
            'engine' : 'SAPI5', #tts引擎
            'rate' : 150,  #朗读速度
            'voice' : 'Microsoft Lili - Chinese (China)' #语音选择
        }
        self.engine = steeltts.SAPI5()
        
    #传递参数
    def set(self, paraName, paraValue):
        b1a = False
        for s1a in self.parameterDict.keys():
            if paraName.upper() == s1a.upper():
                b1a = True
                break
        if b1a == True:
            #如果参数数据类型不匹配则返回fale
            if self.parameterDict[s1a] != None and paraValue != None:
                if type(self.parameterDict[s1a]) != type(paraValue):
                    return(False )
            #如果不是不需重置的参数则断开数据库连接
            b2a = False
            for s3a in ['rate','voice']:
                if paraName.upper() == s3a.upper():
                    b2a = True
                    break
            if b2a == False:
                if self.parameterDict[s1a] != paraValue:
                    try:
                        self.engine.stop()
                    except:
                        pass
            self.parameterDict[s1a] = paraValue
            if s1a == 'engine':
                if paraValue.upper() == 'SAPI5':
                    self.engine = steeltts.SAPI5()
                elif paraValue.upper() == 'ESPEAK':
                    self.engine = steeltts.eSpeak()
                elif paraValue.upper() == 'FREETTS':
                    self.engine = steeltts.FreeTTS()
                else:
                    return(False)
            else:
                try:
                    self.engine.set(s1a, paraValue)
                except:
                    return(False)
            return(True)
        return(False)
        
    def speak(self, words):
        try:
            self.engine.speak(words)
        except:
            s2a = self.set('engine', self.parameterDict['engine'])
            if s2a == True: 
                try:
                    self.engine.speak(words)
                except:
                    return(False)
            elif s2a == False:
                return(False)
            else:
                return(s2a)
        return(True)

class PlayAudio(object):
    def __init__(self):
        self.parameterDict = {
            'audioType' : 'wav', #音频类型
            'filePath' : 'audio',  #文件路径
            'fileName' : 'checkOk.wav',  #文件名
            'chunkSize' : 1024 #文件每次读取大小
        }
        self.pyaudioObj = pyaudio.PyAudio()
        if self.prepare() == False:
            return(False)
    #传递参数
    def set(self, paraName, paraValue):
        b1a = False
        for s1a in self.parameterDict.keys():
            if paraName.upper() == s1a.upper():
                b1a = True
                break
        if b1a == True:
            #如果参数数据类型不匹配则返回fale
            if self.parameterDict[s1a] != None and paraValue != None:
                if type(self.parameterDict[s1a]) != type(paraValue):
                    return(False )
            self.parameterDict[s1a] = paraValue
        return(True)
        
    def prepare(self):
        self.bytesIOCache = BytesIO()
        #生成有效文件路径和名称
        if len(self.parameterDict['fileName'])> 0:
            if len(self.parameterDict['filePath'])> 0:
                pathAndName = self.parameterDict['filePath'] + '\\' + self.parameterDict['fileName']
            else:
                pathAndName = self.parameterDict['fileName']
        else:
            return(False)
        f1 = None
        try:
            f1 = open(pathAndName, 'rb')
        except:
            return(False)
        finally:
            if f1:
                self.bytesIOCache.write(f1.read())                
                f1.close()
        
            
    def play(self, *fileName):
        #检查文件路径和名称
        if len(fileName) > 0:
            s2a, s2b = os.path.split(fileName[0])
            if len(s2a) > 0:
                self.parameterDict['filePath'] = s2a
            if len(s2b) > 0:
                self.parameterDict['fileName'] = s2b
            #进行缓存预处理
            if self.prepare() == False:
                return(False)
        self.bytesIOCache.seek(0)
        if self.parameterDict['audioType'] == 'wav':
            self.audioCache = wave.open(self.bytesIOCache, 'rb')
            nchannels, sampwidth, framerate, nframes = self.audioCache.getparams()[:4]
            self.outputDevice = self.pyaudioObj.open(\
                channels = nchannels,\
                format = self.pyaudioObj.get_format_from_width(sampwidth), \
                rate = framerate,\
                output = True)
        #播放不超过10M的音频数据
        for n1a in range(1073741823):
            if self.parameterDict['chunkSize'] * n1a > 1073741823:
                break
            data = self.audioCache.readframes(self.parameterDict['chunkSize'])
            if data == b'':
                break
            self.outputDevice.write(data)
        self.audioCache.close()
        self.outputDevice.stop_stream()
        self.outputDevice.close()
        return(True)
    
    def close(self):        
        self.pyaudioObj.terminate()
        self.bytesIOCache.close()
